#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""

We have 5 tables related to authentication and authorization.  Each of
these tables is represented in this module by a class with SQL
statement attributes.

The primary table is auth_login.  It has attributes for the login
(username), full name, and (hashed) password.  Example:

    +----------+--------+---------------+----------------+
    | login_id | login  | password_hash | login_name     |
    +----------+--------+---------------+----------------+
    |        1 | alice  | ...           | Alice Walton   |
    |        2 | bob    | ...           | Robert Jordon  |
    +----------+--------+---------------+----------------+

Logins can belong to zero or more groups.  Groups are used to lump
roles together for easier administration.  An example of groups:

    +----------+-----------------+
    | group_id | group_name      |
    +----------+-----------------+
    |       30 | Administrators  |
    |       31 | Content Editors |
    +----------+-----------------+

Logins and groups are associated by way of the auth_login_group mixin
table.  To continue the example, here we see Alice as a member of
Administrators and Content Editors, while Bob is a member of Content
Editors only:

    +----------+----------+
    | login_id | group_id |
    +----------+----------+
    |        1 |       30 |
    |        1 |       31 |
    |        2 |       31 |
    +----------+----------+

Joined and expaned to include names, the groups might look like this:

    +----------+--------+----------+------------------+
    | login_id | login  | group_id | group_name       |
    +----------+--------+----------+------------------+
    |        1 | alice  |       30 | Administrators   |
    |        1 | alice  |       31 | Content Editors  |
    |        2 | bob    |       31 | Content Editors  |
    +----------+--------+----------+------------------+

Roles are similar to permissions; they declare what a login can do.
They're also simple:

    +---------+----------------------+
    | role_id | role_name            |
    +---------+----------------------+
    |     500 | Change site settings |
    |     501 | Reset passwords      |
    |     502 | Create documents     |
    |     503 | Change documents     |
    |     504 | Delete documents     |
    +---------+----------------------+

Roles can be assigned (associated) with logins and with groups.  This
means that a user has all roles that their groups have, and they have
any individual roles assigned directly to them.

Direct role assignment happens via the auth_login_role table.  Again
we continue our example, this time by assigning Bob some extra roles:

    +----------+---------+
    | login_id | role_id |
    +----------+---------+
    |        2 |     500 |
    |        2 |     501 |
    +----------+----------+

Expanded version:

    +----------+--------+---------+----------------------+
    | login_id | login  | role_id | role_name            |
    +----------+--------+----------+---------------------+
    |        2 | bob    |     500 | Change site settings |
    |        2 | bob    |     501 | Reset passwords      |
    +----------+--------+----------+---------------------+

Similarly, roles can be associated with groups.  This is done via the
auth_group_role table.  Bare example first:

    +----------+---------+
    | group_id | role_id |
    +----------+---------+
    |       30 |     500 |
    |       30 |     501 |
    |       31 |     502 |
    |       31 |     503 |
    |       31 |     504 |
    +----------+----------+

And joined and extended:

    +----------+-----------------+---------+----------------------+
    | group_id | group_name      | role_id | role_name            |
    +----------+-----------------+---------+----------------------+
    |       30 | Administrators  |     500 | Change site settings |
    |       30 | Administrators  |     501 | Reset passwords      |
    |       31 | Content Editors |     502 | Create document      |
    |       31 | Content Editors |     503 | Change document      |
    |       31 | Content Editors |     504 | Delete document      |
    +----------+-----------------+---------+----------------------+


The classes are:

  * Logins - provides statements for the auth_login table
  * Groups - provides statements for the auth_group table
  * Roles  - provides statements for the auth_role table

  * LoginGroups - provides statements for the auth_login_group table
  * LoginRoles  - provides statements for the auth_login_role table


Here we go!

    >>> import sqlite3
    >>> con = sqlite3.connect(":memory:")
    >>> cur = con.cursor()
    >>> bla = [cur.execute(line) for line in read_auth_ddl()]
    >>> cur = cur.execute(Logins.select_all)
    >>> cur.fetchall()
    []

It would be nice to test the inserts... but alas, the sqlite3 bits
don't like our insert statements.  So fudge:

    >>> res = cur.execute("INSERT INTO auth_login VALUES (1, 'alice', '', 'Alice W')")
    >>> res = cur.execute("INSERT INTO auth_login VALUES (2, 'bob', '', 'Robert J')")
    >>> res = cur.execute("INSERT INTO auth_login VALUES (3, 'carl', '', 'Carl S')")

Now for the real testing.  Select all the logins:

    >>> res = cur.execute(Logins.select_all)
    >>> cur.fetchall()
    [(1, u'alice', u'Alice W', u''), (2, u'bob', u'Robert J', u''), (3, u'carl', u'Carl S', u'')]

Select a login by name:

    >>> res = cur.execute(Logins.select_by_name % "alice")
    >>> cur.fetchall()
    [(1, u'alice', u'Alice W', u'')]

Select a login by id:

    >>> res = cur.execute(Logins.select_by_id % 2)
    >>> cur.fetchall()
    [(2, u'bob', u'Robert J', u'')]

Update a login by id:

    >>> res = cur.execute(Logins.update_by_id % ('robert', 'Bob J', '', 2))
    >>> res = cur.execute(Logins.select_by_id % 2)
    >>> cur.fetchall()
    [(2, u'robert', u'Bob J', u'')]

Update a login by name:

    >>> res = cur.execute(Logins.update_by_name % ('rj', 'RJ', '', 'robert'))

It's gone!

    >>> res = cur.execute(Logins.select_by_name % 'robert')
    >>> cur.fetchall()
    []

Or is it?

    >>> res = cur.execute(Logins.select_by_name % 'rj')
    >>> cur.fetchall()
    [(2, u'rj', u'RJ', u'')]

Make it really go away:

    >>> res = cur.execute(Logins.delete_by_name % 'rj')
    >>> res = cur.execute(Logins.select_by_name % 'rj')
    >>> cur.fetchall()
    []

Make one go away by id:

    >>> res = cur.execute(Logins.delete_by_id % 3)
    >>> res = cur.execute(Logins.select_by_id % 3)
    >>> cur.fetchall()
    []

"""
import os


def read_auth_ddl(reference=__file__, flavor=None):
    fn = os.path.join(os.path.dirname(reference), '..', 'sql', 'auth.sql')
    data = open(fn).read()
    if flavor in (None, 'sqlite3'):
        data = data.replace('DROP TABLE', 'DROP TABLE IF EXISTS')
        data = data.replace(' IDENTITY ', ' ')
        for line in data.split(';'):
            yield line


def get_roles(connection, login):
    cursor = connection.cursor()
    cursor.execute(LoginGroupRoles.select_by_name % login)
    return sorted([row[0] for row in cursor.fetchall()])


class LoginGroupRoles(object):
    select_by_name = """
    SELECT DISTINCT role_name
    FROM auth_login_group_roles
    WHERE login = '%s'
    """


class Logins(object):
    """ Common SQL statements for the auth_login table.

    """
    insert = """
    INSERT INTO auth_login (login, login_name, password_hash) 
    VALUES ('%s', '%s', '%s')
    """

    select_all = """
    SELECT login_id, login, login_name, password_hash 
    FROM auth_login
    """

    select_by_name = """
    SELECT login_id, login, login_name, password_hash 
    FROM auth_login
    WHERE login = '%s'
    """

    select_by_id = """
    SELECT login_id, login, login_name, password_hash 
    FROM auth_login
    WHERE login_id = %i
    """

    update_by_id = """
    UPDATE auth_login SET login = '%s', login_name = '%s', password_hash = '%s'
    WHERE login_id = %i
    """

    update_by_name = """
    UPDATE auth_login SET login = '%s', login_name = '%s', password_hash = '%s'
    WHERE login = '%s'
    """

    delete_by_id = """
    DELETE FROM auth_login
    WHERE login_id = %i
    """

    delete_by_name = """
    DELETE FROM auth_login
    WHERE login = '%s'
    """


class Groups(object):
    insert = """
    INSERT INTO auth_group (group_name) VALUES ('%s')
    """

    select_all = """
    SELECT group_id, group_name 
    FROM auth_group
    """

    select_by_id = """
    SELECT group_id, group_name 
    FROM auth_group
    WHERE group_id = %i
    """

    select_by_name = """
    SELECT group_id, group_name 
    FROM auth_group
    WHERE group_name = '%s'
    """

    update = """
    UPDATE auth_group SET group_name = '%s' WHERE group_name = '%s'
    """

    delete_by_id = """
    DELETE FROM auth_group WHERE group_id = %i
    """

    delete_by_name = """
    DELETE FROM auth_group WHERE group_name = '%s'
    """


class Roles(object):
    insert = """
    INSERT INTO auth_role (role_name) VALUES ('%s')
    """

    select_all = """
    SELECT role_id, role_name 
    FROM auth_role
    """

    select_by_id = """
    SELECT role_id, role_name 
    FROM auth_role
    WHERE role_id = %i
    """

    select_by_name = """
    SELECT role_id, role_name 
    FROM auth_role
    WHERE role_name = '%s'
    """

    update = """
    UPDATE auth_role SET role_name = '%s' WHERE role_name = '%s'
    """

    delete_by_id = """
    DELETE FROM auth_role WHERE role_id = %i
    """

    delete_by_name = """
    DELETE FROM auth_role WHERE role_name = '%s'
    """


class LoginGroups(object):
    insert = """
    INSERT INTO auth_login_group (login_id, group_id) 
    VALUES (%i, %i)
    """

    select_all = """
    SELECT L.login_id, L.login, L.login_name, G.group_id, G.group_name
    FROM auth_login L 
    INNER JOIN auth_login_group LG ON L.login_id = LG.login_id
    INNER JOIN auth_group G ON LG.group_id = G.group_id
    """

    select_by_login = select_all + """
    WHERE L.login = '%s'
    """

    select_by_login_id = select_all + """
    WHERE L.login_id = %i
    """

    delete = """
    DELETE FROM auth_login_group 
    WHERE login_id = %i AND group_id = %i
    """


class LoginRoles(object):
    insert = """
    INSERT INTO auth_login_role (login_id, role_id) VALUES (%i, %i)
    """

    select_all = """
    SELECT R.role_id, R.role_name
    FROM auth_login AS L
    INNER JOIN auth_login_role AS LR ON L.login_id = LR.login_id
    INNER JOIN auth_role AS R ON LR.role_id = R.role_id
    """

    select_by_login = select_all + """
    WHERE L.login = '%s'
    """

    select_by_login_id = select_all + """
    WHERE L.login_id = %i
    """

    delete = """
    DELETE FROM auth_login_role
    WHERE login_id = %i AND role_id = %i
    """


if __name__ == '__main__':
    import doctest
    doctest.testmod()
